Skip to main content

MySQL Database Management (Internal)

Overview​

This guide covers creating and managing MySQL databases for clients using DirectAdmin.

For Team: Creating Databases​

Creating a Database​

Step 1: Access DirectAdmin

  • Login to DirectAdmin reseller panel
  • Navigate to client's account
  • Or have client do it themselves

Step 2: MySQL Management

  • In Account Manager section, click "MySQL Management"
  • Or type "MySQL Management" in navigation filter

MySQL Management

Step 3: Create New Database

  • Click "Create new database"
  • Enter database name (lowercase, no spaces)
  • Set password (or auto-generate)
  • Click "Create"

Database name format:

  • Format: username_dbname
  • Example: clientname_wordpress
  • DirectAdmin automatically prefixes with username

Creating Database Users​

Step 4: Select Database

  • View list of databases
  • Select the database you want to add user to

Select Database

Step 5: Create New User

  • Under "Users" section, click "Create New User"

Create New User

Step 6: User Details

  • Enter username (will be prefixed like: username_dbuser)
  • Enter password or use "Generate new password" button
  • Click "Create"

Fill User Details

Step 7: Copy Credentials

  • Save database details:
    • Database name
    • Username
    • Password
    • Host (usually localhost)
  • Store securely or provide to client

Database Connection Details​

Standard configuration for clients:

Database Host: localhost
Database Name: username_dbname
Database User: username_dbuser
Database Password: [password set]
Port: 3306 (default)
Remote Access

If client needs remote MySQL access, you'll need to add their IP address in MySQL Management → Access Hosts

Managing Databases​

Accessing phpMyAdmin​

For database management:

  1. DirectAdmin → MySQL Management
  2. Click database name
  3. Click "phpMyAdmin" button
  4. Manage tables, run queries, import/export

Modifying User Permissions​

Grant specific permissions:

  1. Select database
  2. Click on username
  3. Choose permissions (SELECT, INSERT, UPDATE, DELETE, etc.)
  4. Save

Full access (typical):

  • SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER

Read-only access:

  • SELECT only

Changing Database Password​

  1. MySQL Management
  2. Select database
  3. Click on username
  4. Enter new password
  5. Update client's configuration files with new password

Deleting Database/User​

Data Loss

Deleting a database is permanent and cannot be undone!

Before deleting:

  • Confirm with client
  • Take backup first
  • Verify they don't need the data

To delete:

  1. MySQL Management
  2. Select database or user
  3. Click delete icon
  4. Confirm deletion

Remote MySQL Access​

Enabling Remote Access​

When client needs external access (e.g., from their local machine):

  1. Get client's IP address

    • Ask them to visit: whatismyip.com
    • Note their public IP

  2. Add Access Host

    • MySQL Management → Access Hosts
    • Enter client's IP address
    • Or use % for any IP (not recommended - security risk)
    • Click "Add"

  3. Provide connection details

Host: da.alfieweb.com (or server IP)
Port: 3306
Database: username_dbname
User: username_dbuser
Password: [password]

Security note:

  • Remote access increases security risk
  • Only enable when necessary
  • Use strong passwords
  • Consider removing after use
  • Monitor for unauthorized access

Testing Remote Connection​

Using MySQL command line:

mysql -h da.alfieweb.com -P 3306 -u username_dbuser -p username_dbname

Using MySQL Workbench:

  • Create new connection
  • Enter hostname, port, username, database
  • Test connection

Common Database Tasks​

WordPress Database Setup​

For WordPress installations:

  1. Create database: username_wp
  2. Create user: username_wpuser
  3. Provide client with details

In wp-config.php:

define( 'DB_NAME', 'username_wp' );
define( 'DB_USER', 'username_wpuser' );
define( 'DB_PASSWORD', 'password_here' );
define( 'DB_HOST', 'localhost' );

Importing Database​

Via phpMyAdmin:

  1. Access phpMyAdmin
  2. Select database
  3. Click "Import" tab
  4. Choose SQL file
  5. Click "Go"

Via command line (faster for large databases):

mysql -u username_dbuser -p username_dbname < backup.sql

File size limits:

  • phpMyAdmin: Usually 50-128MB limit
  • For larger: Use command line or increase PHP limits
  • Split large files if needed

Exporting/Backing Up Database​

Via phpMyAdmin:

  1. Select database
  2. Click "Export" tab
  3. Choose "Quick" or "Custom"
  4. Select format (usually SQL)
  5. Click "Go"

Via command line:

mysqldump -u username_dbuser -p username_dbname > backup.sql

Automated backups:

  • DirectAdmin daily backups include databases
  • Manual backups before major changes
  • Test restoration periodically

Optimizing Database​

When client reports slow database:

Via phpMyAdmin:

  1. Select database
  2. Check all tables
  3. Click "Optimize table"

Via command line:

mysqlcheck -o username_dbname -u username_dbuser -p

Repair corrupted tables:

mysqlcheck -r username_dbname -u username_dbuser -p

Troubleshooting​

"Error establishing database connection"​

Common causes:

  1. Wrong credentials
  2. Database doesn't exist
  3. User not added to database
  4. MySQL service down

Solution:

  1. Verify credentials in config file
  2. Check database exists in DirectAdmin
  3. Verify user has permissions
  4. Check MySQL running: systemctl status mysqld

"Too many connections"​

Cause: MySQL connection limit reached

Check:

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

Solution:

  • Close idle connections
  • Optimize application connection handling
  • Increase max_connections (if needed)
  • Investigate connection leaks

"Access denied for user"​

Causes:

  • Wrong password
  • Wrong username
  • User not granted access to database
  • Connecting from unauthorized host

Solution:

  1. Verify username/password correct
  2. Check user exists and has database permissions
  3. For remote: Verify IP in Access Hosts

Database Locked / Tables Crashed​

Symptoms: Can't read/write to tables

Solution:

# Repair tables
mysqlcheck -r --all-databases -u root -p

# Or in phpMyAdmin: Select table → Repair table

Slow Queries​

Diagnose:

  1. Enable slow query log
  2. Review query performance
  3. Check for missing indexes
  4. Optimize problematic queries

Common issues:

  • Missing indexes on large tables
  • Poorly written queries
  • Large result sets
  • Too many JOINs

Solutions:

  • Add indexes
  • Optimize queries
  • Enable query cache
  • Use caching layers (Redis, Memcached)

Best Practices​

For Team​

  1. Naming convention:

    • Use descriptive names
    • Keep consistent format
    • Document purpose

  2. Security:

    • Strong passwords always
    • Limit remote access
    • Regular backups
    • Monitor for suspicious activity

  3. Performance:

    • Regular optimization
    • Monitor database sizes
    • Clean up old/unused databases
    • Index appropriately

  4. Documentation:

    • Note database purpose in client records
    • Document custom configurations
    • Keep credentials in password manager

For Clients (Guide them)​

  1. Use prepared statements - Prevent SQL injection
  2. Close connections - Don't leave hanging
  3. Backup regularly - Before major changes
  4. Optimize queries - Index properly
  5. Clean up data - Remove old/unused records
  6. Monitor size - Keep within plan limits

Database Quotas​

Check database sizes:

# Login to MySQL as admin
mysql -u root -p

# Check all database sizes
SELECT 
  table_schema AS "Database",
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

If client exceeds quota:

  1. Notify client
  2. Suggest optimization/cleanup
  3. Offer plan upgrade
  4. Monitor growth trends

Advanced: Managing from Command Line​

For team use:

List all databases:

mysql -u root -p -e "SHOW DATABASES;"

Create database:

mysql -u root -p -e "CREATE DATABASE username_dbname;"

Create user:

mysql -u root -p -e "CREATE USER 'username_dbuser'@'localhost' IDENTIFIED BY 'password';"

Grant permissions:

mysql -u root -p -e "GRANT ALL PRIVILEGES ON username_dbname.* TO 'username_dbuser'@'localhost';"
mysql -u root -p -e "FLUSH PRIVILEGES;"

Delete database:

mysql -u root -p -e "DROP DATABASE username_dbname;"

Quick Reference​

TaskLocation in DirectAdmin
Create databaseMySQL Management → Create new database
Create userSelect database → Create New User
phpMyAdmin accessClick database → phpMyAdmin button
Remote accessMySQL Management → Access Hosts
View sizeMySQL Management (shows size per DB)
Backup databasephpMyAdmin → Export
Import databasephpMyAdmin → Import

Support Scenarios​

Client: "I need a database for WordPress" → Create database, user, provide credentials, guide through wp-config.php

Client: "Database connection error" → Verify credentials, check database/user exists, test connection

Client: "Need to import large database" → Use command line or increase limits, offer to do it for them

Client: "Need remote access" → Get their IP, add to Access Hosts, provide connection details, security warning

Client: "Database slow" → Check size, optimize tables, review queries, consider upgrade

Tip: Most clients can manage their own databases via DirectAdmin. Only intervene when they have issues or need assistance.